package cn.ylyhappy.ideavimbuilder4.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.NonNull;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import cn.ylyhappy.ideavimbuilder4.IdeaVimBuilder;
import cn.ylyhappy.ideavimbuilder4.LogDelegate;
import cn.ylyhappy.ideavimbuilder4.exceptions.DatabaseException;
import cn.ylyhappy.ideavimbuilder4.model.IdeaVimAction;
import cn.ylyhappy.ideavimbuilder4.model.IdeaVimConfig;
import cn.ylyhappy.ideavimbuilder4.model.IdeaVimLine;
import cn.ylyhappy.ideavimbuilder4.model.LineDTO;

public class DbHelper extends SQLiteOpenHelper {

    // Database name
    private static final String DATABASE_NAME = "ideavim.db";
    // Database version aligned if possible to software version
    private static final int DATABASE_VERSION = 100;
    // Sql query file directory
    private static final String SQL_DIR = "sql";



    public static final String TABLE_LINE = "Line";

    public static final String LINE_ID = "Line.id";
    public static final String LINE_ACTION_ID = "Line.action_id";
    public static final String LINE_MAP = "Line.map";
    public static final String LINE_ACTIONPRE = "Line.actionpre";
    public static final String LINE_SHORTCUT = "Line.shortcut";
    public static final String LINE_ACTIONAFTER = "Line.actionafter";
    public static final String LINE_CR = "Line.cr";
    public static final String LINE_SOURCE = "Line.source";
    public static final String NOTABLE_LINE_ID = "id";
    public static final String NOTABLE_LINE_ACTION_ID = "action_id";
    public static final String NOTABLE_LINE_MAP = "map";
    public static final String NOTABLE_LINE_ACTIONPRE = "actionpre";
    public static final String NOTABLE_LINE_SHORTCUT = "shortcut";
    public static final String NOTABLE_LINE_ACTIONAFTER = "actionafter";
    public static final String NOTABLE_LINE_CR = "cr";
    public static final String NOTABLE_LINE_SOURCE = "source";




    public static final String TABLE_ACTION = "Action";

    public static final String ACTION_ID = "Action.id";
    public static final String ACTION_EN = "Action.en";
    public static final String ACTION_ZH = "Action.zh";
    public static final String ACTION_TAG = "Action.tag";
    public static final String NOTABLE_ACTION_ID = "id";
    public static final String NOTABLE_ACTION_EN = "en";
    public static final String NOTABLE_ACTION_ZH = "zh";
    public static final String NOTABLE_ACTION_TAG = "tag";




    public static final String TABLE_CONFIG = "Config";

    public static final String CONFIG_ID = "Config.id";
    public static final String CONFIG_LINE_ID = "Config.line_id";
    public static final String CONFIG_COMMENT = "Config.comment";
    public static final String CONFIG_NAME = "Config.name";
    public static final String NOTABLE_CONFIG_ID = "id";
    public static final String NOTABLE_CONFIG_LINE_ID = "line_id";
    public static final String NOTABLE_CONFIG_COMMENT = "comment";
    public static final String NOTABLE_CONFIG_NAME = "name";




    public static final String TABLE_CONFIGANDLINE = "ConfigAndLine";

    public static final String CONFIGANDLINE_ID = "ConfigAndLine.id";
    public static final String CONFIGANDLINE_CONFIG_ID = "ConfigAndLine.config_id";
    public static final String CONFIGANDLINE_LINE_ID = "ConfigAndLine.line_id";
    public static final String NOTABLE_CONFIGANDLINE_ID = "id";
    public static final String NOTABLE_CONFIGANDLINE_CONFIG_ID = "config_id";
    public static final String NOTABLE_CONFIGANDLINE_LINE_ID = "line_id";



    // Queries
    private static final String CREATE_QUERY = "create.sql";
    private static final String UPGRADE_QUERY_PREFIX = "upgrade-";
    private static final String UPGRADE_QUERY_SUFFIX = ".sql";


    private final Context mContext;

    private static DbHelper instance = null;
    private SQLiteDatabase db;

    public static synchronized DbHelper getInstance() {
        return getInstance(IdeaVimBuilder.getAppContext());
    }


    public static synchronized DbHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DbHelper(context);
        }
        return instance;
    }


    public static synchronized DbHelper getInstance(boolean forcedNewInstance) {
        if (instance == null || forcedNewInstance) {
            Context context = (instance == null || instance.mContext == null) ? IdeaVimBuilder.getAppContext()
                    : instance.mContext;
            instance = new DbHelper(context);
        }
        return instance;
    }


    private DbHelper(Context mContext) {
        super(mContext, DATABASE_NAME, null, DATABASE_VERSION);
        this.mContext = mContext;
    }

    public String getDatabaseName() {
        return DATABASE_NAME;
    }

    public SQLiteDatabase getDatabase() {
        return getDatabase(false);
    }

    public SQLiteDatabase getDatabase(boolean forceWritable) {
        try {
            return forceWritable ? getWritableDatabase() : getReadableDatabase();
        } catch (IllegalStateException e) {
            return this.db;
        }
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        //db.disableWriteAheadLogging();
        super.onOpen(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            LogDelegate.i("Database creation");
            execSqlFile(CREATE_QUERY, db);

        } catch (IOException e) {
            throw new DatabaseException("Database creation failed: " + e.getMessage(), e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }


    private void execSqlFile(String sqlFile, SQLiteDatabase db) throws SQLException, IOException {
        LogDelegate.i("  exec sql file: {}" + sqlFile);
        for (String sqlInstruction : SqlParser
                .parseSqlFile(SQL_DIR + "/" + sqlFile, mContext.getAssets())) {
            LogDelegate.v("    sql: {}" + sqlInstruction);
            try {
                db.execSQL(sqlInstruction);
            } catch (Exception e) {
                LogDelegate.e("Error executing command: " + sqlInstruction, e);
            }
        }
    }

    /**
     * 对IdeaVimAction的查询操作
     */
    public List<IdeaVimAction> getIdeaVimActions() {
        List<IdeaVimAction> IdeaVimActions = new ArrayList<>();


        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ").append(ACTION_ID)
        .append(',').append(ACTION_EN)
        .append(',').append(ACTION_ZH)
        .append(',').append(ACTION_TAG)
        .append(" FROM ").append(TABLE_ACTION);
        String query = sb.toString();
        LogDelegate.v("Query: " + query);

        try (Cursor cursor = getDatabase().rawQuery(query, null)) {

            if (cursor.moveToFirst()) {
                do {
                    int i = 0;
                    IdeaVimAction ideaVimAction = new IdeaVimAction();
                    ideaVimAction.setId(cursor.getInt(i++));
                    ideaVimAction.setEn(cursor.getString(i++));
                    ideaVimAction.setZh(cursor.getString(i++));
                    ideaVimAction.setSelected(false);
                    
                    IdeaVimActions.add(ideaVimAction);

                } while (cursor.moveToNext());
            }

        }

        LogDelegate.v("Query: Retrieval finished!");
        return IdeaVimActions;
    }

    public String getIdeaVimActionByIdQueryString(long id){
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ").append(ACTION_ID)
                .append(',').append(ACTION_EN)
                .append(',').append(ACTION_ZH)
                .append(',').append(ACTION_TAG)
                .append(" FROM ").append(TABLE_ACTION)
                .append("WHERE ").append(ACTION_ID)
                .append(" = ").append(id);
        return sb.toString();
    }

    /**
     * 对IdeaVimAction的查询操作
     */
    public IdeaVimAction getIdeaVimActionById(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ").append(ACTION_ID)
                .append(',').append(ACTION_EN)
                .append(',').append(ACTION_ZH)
                .append(',').append(ACTION_TAG)
                .append(" FROM ").append(TABLE_ACTION)
                .append("WHERE ").append(ACTION_ID)
                .append(" = ").append(id);
        String query = sb.toString();
        LogDelegate.v("Query: " + query);

        IdeaVimAction ideaVimAction = new IdeaVimAction();
        try (Cursor cursor = getDatabase().rawQuery(query, null)) {

            if (cursor.moveToFirst()) {
                do {
                    int i = 0;
                    ideaVimAction.setId(cursor.getInt(i++));
                    ideaVimAction.setEn(cursor.getString(i++));
                    ideaVimAction.setZh(cursor.getString(i++));
                    ideaVimAction.setSelected(false);

                } while (cursor.moveToNext());
            }

        }

        LogDelegate.v("Query: Retrieval finished!");
        return ideaVimAction;
    }


    /**
     * 对IdeaVimConfig的查询操作
     */
    public List<IdeaVimConfig> getIdeaVimConfigs() {
        List<IdeaVimConfig> IdeaVimConfigs = new ArrayList<>();


        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ").append(CONFIG_ID)
        .append(',').append(CONFIG_COMMENT)
        .append(',').append(CONFIG_NAME)
        .append(" FROM ").append(TABLE_CONFIG);
        String query = sb.toString();
        LogDelegate.v("Query: " + query);

        try (Cursor cursor = getDatabase().rawQuery(query, null)) {

            if (cursor.moveToFirst()) {
                do {
                    int i = 0;
                    IdeaVimConfig ideaVimConfig = new IdeaVimConfig();
                    ideaVimConfig.setId(cursor.getInt(i++));
                    ideaVimConfig.setComment(cursor.getString(i++));
                    ideaVimConfig.setName(cursor.getString(i++));
                    ideaVimConfig.setSelected(false);
                    
                    IdeaVimConfigs.add(ideaVimConfig);

                } while (cursor.moveToNext());
            }

        }

        LogDelegate.v("Query: Retrieval finished!");
        return IdeaVimConfigs;
    }

    /**
     * Common method for notes retrieval. It accepts a query to perform and returns matching records.
     */
    public ArrayList<Integer> getActionIdsByConfigId(int configId) {
        ArrayList<Integer> res = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ").append(LINE_ACTION_ID)
                .append(" FROM ").append(TABLE_CONFIGANDLINE)
                .append(" INNER JOIN ").append(TABLE_LINE)
                .append(" ON ").append(CONFIGANDLINE_CONFIG_ID).append(" = ").append(configId)
                .append(" AND ").append(LINE_ID).append(" = ").append(CONFIGANDLINE_LINE_ID);
        String query = sb.toString();
        LogDelegate.v("Query: " + query);

        try (Cursor cursor = getDatabase().rawQuery(query, null)) {

            if (cursor.moveToFirst()) {
                do {
                    res.add(cursor.getInt(0));
                } while (cursor.moveToNext());
            }

        }

        LogDelegate.v("Query: Retrieval finished!");
        return res;
    }

    /**
     * Common method for notes retrieval. It accepts a query to perform and returns matching records.
     */
    public String getPreviewIdeaVimConfig(int config_id, boolean order) {
        StringBuilder res = new StringBuilder();
        String query = getPreivewIdeaVimConfigString(config_id);
        LogDelegate.v("Query: " + query);

        try (Cursor cursor = getDatabase().rawQuery(query, null)) {

            if (cursor.moveToFirst()) {
                do {
                    int i = 0;
                    String map = cursor.getString(i++);
                    String pre = cursor.getString(i++);
                    String en = cursor.getString(i++);
                    String zh = cursor.getString(i++);
                    String aft = cursor.getString(i++);
                    String st = cursor.getString(i++);
                    String cr = cursor.getString(i);
                    res.append("# ")
                            .append(zh).append('\n')
                            .append(map).append('\t')
                            .append(pre).append('\t')
                            .append(en).append('\t')
                            .append(aft).append('\t')
                            .append(st).append('\t')
                            .append(cr).append('\n');
                } while (cursor.moveToNext());
            }

        }

        LogDelegate.v("Query: Retrieval finished!");
        return res.toString();
    }

    private static @NonNull String getPreivewIdeaVimConfigString(int config_id) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ").append(LINE_MAP)
                .append(',').append(LINE_ACTIONPRE)
                .append(',').append(ACTION_EN)
                .append(',').append(ACTION_ZH)
                .append(',').append(LINE_ACTIONAFTER)
                .append(',').append(LINE_SHORTCUT)
                .append(',').append(LINE_CR)
                .append(" FROM ").append(TABLE_CONFIG)
                .append(" INNER JOIN ").append(TABLE_CONFIGANDLINE)
                .append(" ON ").append(CONFIG_ID).append(" = ").append(config_id).append(" AND ")
                .append(CONFIG_ID).append(" = ").append(CONFIGANDLINE_CONFIG_ID)
                .append(" INNER JOIN ").append(TABLE_LINE)
                .append(" ON ").append(LINE_ID).append(" = ").append(CONFIGANDLINE_LINE_ID)
                .append(" INNER JOIN ").append(TABLE_ACTION)
                .append(" ON ").append(ACTION_ID).append(" = ").append(LINE_ACTION_ID);

        String query = sb.toString();
        return query;
    }

    public long insertLine(LineDTO lineDTO) {
        // config id;
        // Line
        // ConfigAndLine
        getDatabase().beginTransaction();
        try {
            ContentValues line = new ContentValues();
            line.put(NOTABLE_LINE_ACTION_ID, lineDTO.getActionId());
            line.put(NOTABLE_LINE_MAP, lineDTO.getMap());
            line.put(NOTABLE_LINE_ACTIONPRE, lineDTO.getActionPre());
            line.put(NOTABLE_LINE_SHORTCUT, lineDTO.getShortcut());
            line.put(NOTABLE_LINE_ACTIONAFTER, lineDTO.getActionaft());
            line.put(NOTABLE_LINE_CR, lineDTO.getCr());
            long lineId = getDatabase().insert(TABLE_LINE, "", line);
            ContentValues configAndLine = new ContentValues();
            configAndLine.put(NOTABLE_CONFIGANDLINE_CONFIG_ID, lineDTO.getConfigId());
            configAndLine.put(NOTABLE_CONFIGANDLINE_LINE_ID, lineId);
            getDatabase().insert(TABLE_CONFIGANDLINE, NOTABLE_CONFIGANDLINE_CONFIG_ID, configAndLine);
            LogDelegate.v("Insert" + line);
            LogDelegate.v("Insert" + configAndLine);
            getDatabase().setTransactionSuccessful();
            LogDelegate.v("添加数据成功, LindId is " + lineId);
            return lineId;
        } catch (SQLException e) {
            e.printStackTrace();
            LogDelegate.e("插入 LINE 数据失败");
        } finally {
            getDatabase().endTransaction();
        }
        return -1;
    }

}
